package de.ihk.beans;

import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.faces.event.ActionEvent;
import javax.faces.model.SelectItem;
import javax.swing.JFileChooser;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;


import de.ihk.data.Student;
import de.ihk.datamanagment.MysqlConnector;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

@ManagedBean
@ViewScoped
public class StudentenBean implements Serializable {

	private static final long serialVersionUID = 1L;

	private final static String[] berufe;
	private final static String[] ort;
	private final static String[] firma;
	private final static String[] name;
	private final static String[] vorname;
	static {
		berufe = new String[5];
		berufe[0] = "Industriemechaniker";
		berufe[1] = "Konstruktionsmechaniker";
		berufe[2] = "Werkzeugmechaniker";
		berufe[3] = "Zerspanungsmechaniker";
		berufe[4] = "Chemielaborant";

		ort = new String[5];
		ort[0] = "Clausthal-Zellerfeld";
		ort[1] = "Seesen";
		ort[2] = "Goslar";
		ort[3] = "Braunschweig";
		ort[4] = "Salzgitter";

		firma = new String[5];
		firma[0] = "CUTEC";
		firma[1] = "CROWN";
		firma[2] = "Ausbildungsverbund";
		firma[3] = "SMAG";
		firma[4] = "Fuchs";

		name = new String[5];
		name[0] = "Muster";
		name[1] = "Meier";
		name[2] = "Weber";
		name[3] = "Müller";
		name[4] = "Meister";

		vorname = new String[5];
		vorname[0] = "Max";
		vorname[1] = "Sebastian";
		vorname[2] = "Dieter";
		vorname[3] = "Rudolf";
		vorname[4] = "Julian";
	}
	private List<Student> studenten;
	private Student[] selectedStudents;
	private SelectItem[] berufOptions;
	private List<Student> filteredStudents;

	private int pruefnr = 0;
	private String newVorname;
	private String newNachname;
	private String newFirma;
	private String newOrt;
	private String newBeruf;
	private String newbbs;

	private ResultSet rs = null;
	private MysqlConnector conn = null;

	File inputWorkbook;
	Workbook w;

	public StudentenBean() throws ClassNotFoundException, SQLException {
		studenten = new ArrayList<Student>();
		System.out.println("Studenten werden erstellt");
		conn = new MysqlConnector();
		berufOptions = createFilterOptions(berufe);
		updateList();
	}

	public void submit(ActionEvent actionEvent) {
		FacesContext context = FacesContext.getCurrentInstance();
		System.out.println(pruefnr);
		System.out.println(newVorname + " " + newNachname);
		try {
			conn.addStudentToDb(pruefnr, newNachname, newVorname, newFirma, newOrt, newBeruf, newbbs);
			updateList();
		} catch (SQLException e) {
			context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Fehler", e.getMessage()));
			e.printStackTrace();
		}
	}

	public void deleteSelected(ActionEvent actionEvent) throws SQLException {
		for (Student i : selectedStudents) {
			System.out.println(i.getPruefnr());
			conn.mysqlUpdateQuery("DELETE FROM students WHERE pruefnr = " + i.getPruefnr() + ";");

		}
		updateList();
	}

	public void updateStudent(ActionEvent actionEvent) {
		FacesContext context = FacesContext.getCurrentInstance();
		System.out.println("Delete STUDENT WHERE pruefnr =" + selectedStudents[0].getPruefnr());
		try {
			conn.mysqlUpdateQuery("DELETE FROM students WHERE pruefnr = " + selectedStudents[0].getPruefnr() + ";");
			System.out.println("UPDATE STUDENT:" + newVorname + " " + newNachname);
			conn.addStudentToDb(pruefnr, newNachname, newVorname, newFirma, newOrt, newBeruf, newbbs);
			updateList();
		} catch (SQLException e) {
			context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Fehler", e.getMessage()));
			e.printStackTrace();
		}

	}

	public void updateList() throws SQLException {
		rs = conn.getStudents();
		studenten.removeAll(studenten);
		while (rs.next()) {
			studenten.add(new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs
					.getString(7)));
		}

	}

	private SelectItem[] createFilterOptions(String[] data) {
		SelectItem[] options = new SelectItem[data.length + 1];

		options[0] = new SelectItem("", "Select");
		for (int i = 0; i < data.length; i++) {
			options[i + 1] = new SelectItem(data[i], data[i]);
		}

		return options;
	}

	public void clearStudentdata(ActionEvent actionEvent) {
		System.out.println("Clearing Input");
		pruefnr = 0;
		newVorname = "";
		newNachname = "";
		newFirma = "";
		newOrt = "";
		newBeruf = "";
		newbbs = "";
	}

	public void bearbeiteSelected(ActionEvent actionEvent) {
		System.out.println("BEARBEITE STUDENT MIT PRÜFNR:" + selectedStudents[0].getPruefnr());
		pruefnr = selectedStudents[0].getPruefnr();
		newVorname = selectedStudents[0].getVorname();
		newNachname = selectedStudents[0].getNachname();
		newFirma = selectedStudents[0].getFirma();
		newOrt = selectedStudents[0].getOrt();
		newBeruf = selectedStudents[0].getBeruf();
		newbbs = selectedStudents[0].getBbs();
		System.out.println(newVorname + " " + newNachname);
	}

	public void populate(ActionEvent actionEvent) {
		System.out.println("POPULATING");
		FacesContext context = FacesContext.getCurrentInstance();
		try {
			for (int i = 0; i < 30; i++) {
				conn.addStudentToDb(i, getRandomName(), getRandomVorname(), getRandomFirma(), getRandomOrt(), getRandomBeruf(), "BBS");
			}
			updateList();
		} catch (SQLException e) {
			context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Fehler", e.getMessage()));
			e.printStackTrace();
		}
	}

	public void deleteAll(ActionEvent actionEvent) throws SQLException {
		conn.mysqlUpdateQuery("TRUNCATE students");
		updateList();
	}

	public void importExcel(ActionEvent actionEvent) throws BiffException, IOException, ClassNotFoundException, InstantiationException,
			IllegalAccessException, UnsupportedLookAndFeelException {
		FacesContext context = FacesContext.getCurrentInstance();
		UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
		final JFileChooser fc = new JFileChooser();
		int returnVal = fc.showOpenDialog(fc);
		File file = null;
		if (returnVal == JFileChooser.APPROVE_OPTION) {
			file = fc.getSelectedFile();
			// This is where a real application would open the file.
			System.out.println(("Opening: " + file.getName() + "."));
		} else {
			System.out.println(("Open command cancelled by user."));
		}
		try {
			if (file != null) {
				inputWorkbook = file;
				w = Workbook.getWorkbook(inputWorkbook);
				// Get the first sheet
				Sheet sheet = w.getSheet(0);
				// Loop over first 10 column and lines
				for (int j = 0; j < sheet.getRows(); j++) {
					Cell cell = sheet.getCell(0, j);
					CellType type = cell.getType();
					if (type == CellType.NUMBER) {
						System.out.println("I got a number " + cell.getContents());
						conn.addStudentToDb(Integer.parseInt(cell.getContents()), sheet.getCell(2, j).getContents(), sheet.getCell(1, j)
								.getContents(), sheet.getCell(3, j).getContents(), sheet.getCell(4, j).getContents(), sheet.getCell(5, j)
								.getContents(), sheet.getCell(6, j).getContents());
					}
				}
			} else {
				System.out.println("didnt work :-/");
			}
			updateList();
		} catch (SQLException e) {
			context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Fehler", e.getMessage()));
			e.printStackTrace();
		}

	}

	// ================== Setters and getters start =====================

	public List<Student> getFilteredStudents() {
		return filteredStudents;
	}

	public void setFilteredStudents(List<Student> filteredStudents) {
		this.filteredStudents = filteredStudents;
	}

	private String getRandomBeruf() {
		return berufe[(int) (Math.random() * 5)];
	}

	private String getRandomOrt() {
		return ort[(int) (Math.random() * 5)];
	}

	private String getRandomFirma() {
		return firma[(int) (Math.random() * 5)];
	}

	private String getRandomName() {
		return name[(int) (Math.random() * 5)];
	}

	private String getRandomVorname() {
		return vorname[(int) (Math.random() * 5)];
	}

	public List<Student> getStudenten() {
		return studenten;
	}

	public Student[] getSelectedStudents() {
		return selectedStudents;
	}

	public void setSelectedStudents(Student[] selectedStudents) {
		this.selectedStudents = selectedStudents;
	}

	public int getPruefnr() {
		return pruefnr;
	}

	public void setPruefnr(int pruefnr) {
		this.pruefnr = pruefnr;
	}

	public String getNewVorname() {
		return newVorname;
	}

	public void setNewVorname(String newVorname) {
		this.newVorname = newVorname;
	}

	public String getNewNachname() {
		return newNachname;
	}

	public void setNewNachname(String newNachname) {
		this.newNachname = newNachname;
	}

	public String getNewFirma() {
		return newFirma;
	}

	public void setNewFirma(String newFirma) {
		this.newFirma = newFirma;
	}

	public String getNewOrt() {
		return newOrt;
	}

	public void setNewOrt(String newOrt) {
		this.newOrt = newOrt;
	}

	public String getNewBeruf() {
		return newBeruf;
	}

	public void setNewBeruf(String newBeruf) {
		this.newBeruf = newBeruf;
	}

	public String getNewbbs() {
		return newbbs;
	}

	public void setNewbbs(String newbbs) {
		this.newbbs = newbbs;
	}

	public SelectItem[] getBerufOptions() {
		return berufOptions;
	}

	public void setBerufOptions(SelectItem[] berufOptions) {
		this.berufOptions = berufOptions;
	}

}
